﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Windows.Forms;
using NHibernate;
using MulagoDb.persistence;
using MulagoDb.model;

namespace MulagoDb
{
    public class PatientRegistration
    {
       public static PersistenceManager persist = new PersistenceManager();
       public static String myConString = "SERVER=localhost;" +
                       "DATABASE=mulago;" +
                       "UID=root;" +
                       "PASSWORD=test";

       public static bool authenticate(string username,string password)
       {
           try
           {

               MySqlConnection connection = new MySqlConnection(myConString);
               MySqlCommand command = connection.CreateCommand();
               MySqlDataReader reader;
               command.CommandText = "select * from user where username = '"+username+"' and password ='"+
                   password+"'";
               connection.Open();
               Console.WriteLine("Successfully connected to database ");
               reader = command.ExecuteReader();
               if (reader.HasRows)
               {
                   connection.Close();
                   return true;
               }
               else
               {
                   connection.Close();
                   return false;
               }
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
               return false;
           }
       }


        public static void getUsers() {
            try
            {

                MySqlConnection connection = new MySqlConnection(myConString);
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "select * from patient";
                connection.Open();
                Console.WriteLine("Successfully connected to database ");
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        thisrow += Reader.GetValue(i).ToString() + ",";
                    }
                    Console.WriteLine(thisrow);
                    Console.WriteLine("\n");
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        public String getPatientName(int id) {
            String name = "";
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "select fname,lname from patient where pid = "+id;
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string thisrow = "";
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    thisrow += reader.GetValue(i).ToString() + " ";
                }
                name = thisrow;
            }
            return name;

        }
       
        public static ArrayList searchPatientName(string name,string tobeSearched)
        {
            ArrayList patient = new ArrayList();
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            if (tobeSearched.Equals("name"))
            {
                command.CommandText = "select * from patient where names like '%" + name + "%'";
            }
            else if (tobeSearched.Equals("scd"))
            {
                command.CommandText = "select * from patient where clinicNum like '%" + name + "%'";
            }
            else if (tobeSearched.Equals("date"))
            {
                command.CommandText = "SELECT * FROM patient where pid = (select distinct(pid) from clinicvisit where date = '" + name + "')";
            }
            else if (tobeSearched.Equals("phone"))
            {
                command.CommandText = "select * from patient where phoneNum like '%" + name + "%'";
            }
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                MulagoDb.model_old.clinicvisit.Patient pat = new MulagoDb.model_old.clinicvisit.Patient();
                pat.setId(reader.GetValue(0).ToString());
                pat.setNames(reader.GetValue(1).ToString());
                pat.setHospitalNumber(reader.GetValue(2).ToString());
                pat.setScd(reader.GetValue(3).ToString());
                pat.setDob(reader.GetValue(4).ToString());
                patient.Add(pat);
            }
            return patient;

        }

        public static bool addPatients(Patient patient)
        {
                try
                {
                    //save hibernate object
                    persist.Save<Patient>(patient);
                    return true;
                }
                catch (Exception ex) {
                    Console.WriteLine("================== error "+ex.Message);
                    return false;
                }
        }
      
        public static bool updatePatient(Patient patient) {
            try
            {
                //save hibernate object
                persist.Save<Patient>(patient);
                return true;
            }
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
                return false;
            }
        
        }
        public static MulagoDb.model_old.clinicvisit.Patient getPatientBioData(int patientId)
        {
            MulagoDb.model_old.clinicvisit.Patient pat = new MulagoDb.model_old.clinicvisit.Patient();
            try
            {
                MySqlConnection connection = new MySqlConnection(myConString);
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM patient WHERE pid = " + patientId;
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    pat.setId(reader.GetValue(0).ToString());
                    pat.setNames(reader.GetValue(1).ToString());
                    pat.setHospitalNumber(reader.GetValue(2).ToString());
                    pat.setScd(reader.GetValue(3).ToString());
                    pat.setDob(reader.GetValue(4).ToString());
                    pat.setPhoneNum(reader.GetValue(5).ToString());
                    pat.setSex(reader.GetValue(6).ToString());
                }
            }
            catch {
                return null;
            }
            return pat;
        }

        public static MulagoDb.model_old.clinicvisit.PatientParent getPatientParents(int patientId, string description)
        {
            MulagoDb.model_old.clinicvisit.PatientParent parents = new MulagoDb.model_old.clinicvisit.PatientParent();
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM parents WHERE patientId = " + patientId+
                " AND description = '"+description+"'";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                parents.setId(reader.GetValue(0).ToString());
                parents.setNames(reader.GetValue(1).ToString());
                parents.setDescription(reader.GetValue(2).ToString());
                parents.setTribe(reader.GetValue(3).ToString());
                parents.setOccupation(reader.GetValue(4).ToString());
            }
            return parents;
        }

        public static MulagoDb.model_old.clinicvisit.PatientAddress getPatientAddress(int patientId)
        {
            MulagoDb.model_old.clinicvisit.PatientAddress address = new MulagoDb.model_old.clinicvisit.PatientAddress();
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM addres WHERE patientId = " + patientId;
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                address.setId(reader.GetValue(1).ToString());
                address.setVillage(reader.GetValue(2).ToString());
                address.setCounty(reader.GetValue(3).ToString());
                address.setDistrict(reader.GetValue(4).ToString());
                address.setTelcareTaker(reader.GetValue(5).ToString());
                address.setTelself(reader.GetValue(6).ToString());
                address.setPersonWhoCares(reader.GetValue(7).ToString());
            }
            return address;
        }
    
    }
}
